if you can’t beat them, join them.
Gregory Y. Titelman
Since Oracle 9i, Oracle SQL supports the ANSI SQL syntax. It takes a bit of getting used to, especially when you are familiar with the Oracle syntax, but it is much more verbose, self-documenting, if you will.
Syntax
Part of the Select syntax is the joining of tables. To join two tables (or views) you have two options. An inner join (both tables should contain the records) or an outer join (records in one or both tables are optional).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<span style="text-decoration: underline;">inner_cross_join_clause</span> table_reference { [ INNER ] JOIN table_reference { ON condition | USING (column [, column ]...) } | { CROSS | NATURAL [ INNER ] } JOIN table_reference } <span style="text-decoration: underline;">outer_join_clause</span> table_reference [ query_partition_clause ] { outer_join_type JOIN | NATURAL [ outer_join_type ] JOIN } table_reference [ query_partition_clause ] [ ON condition | USING ( column [, column ]...) ] |
For this article we will need two tables to show the different methods of joining.
Table A
| Key | Value | Type |
| 1 | Apple | 1 |
| 2 | Pear | 1 |
| 3 | Orange | 1 |
| 4 | Lettuce | 2 |
| 5 | Spinach | 2 |
| 6 | Hamburger | 3 |
| 7 | Steak | 3 |
Table B
| Key | Value |
| 1 | Fruit |
| 2 | Vegetable |
| 3 | Meat |
| 4 | Fish |
Cross Join
A cross join, also called a Cartesian join, is made in Oracle SQL by omitting a join predicate.
|
1 2 3 |
SELECT * FROM a ,b |
If you encounter something like this you are most likely thinking that a join predicate is missing. If you use ANSI SQL you write this as follows:
|
1 2 3 |
SELECT * FROM a CROSS JOIN b |
Implicitly indicating that a Cartesian join is intended. Both approaches give you the same result, but the ANSI version is more self-documenting. In the first version you should actually add a comment indicating that.
Natural Join
When you use natural join you instruct the RDBMS to join on all columns that have the same name. Columns in both tables must have the same value to satisfy the join condition. In the example provided, the query:
|
1 2 3 |
SELECT * FROM a NATURAL JOIN b |
doesn’t return any results since there are no values which are the same in both tables.
Join
There are actually two flavors in this:
JOIN…USING
and
JOIN…ON
When using the first version, you mention the column names to use in the join condition. If you want to use columns in both tables that have the same name, but not all of them (like the natural join) you could execute a query like this:
|
1 2 3 4 |
SELECT * FROM a JOIN b USING (key) |
This way the tables are joined just using the key column to satisfy the condition, whereas the natural join would also take the value column into account.
If the column names to be used in the join condition are different in both tables (which is most common, I think) then you would want to use the JOIN…ON syntax.
|
1 2 3 |
SELECT * FROM a JOIN b ON (a.type = b.key) |
This way you tell the SQL engine exactly which conditions to use for the join. The names can be different in both tables.
Left/Right Outer Join
One thing that takes the most getting used to is the outer join. In Oracle SQL you are probably used to writing something like this:
|
1 2 3 4 |
SELECT * FROM a ,b WHERE a.type(+) = b.key |
where the (+) indicates that the values don’t need to exist in the table to get a row in the result set. If you put the (+) sign on the other side of the equation you indicate that rows in the other table are optional.
|
1 2 3 4 |
SELECT * FROM a ,b WHERE a.type = b.key(+) |
In ANSI SQL you use the LEFT or RIGHT OUTER JOIN syntax.
|
1 2 3 |
SELECT * FROM a RIGHT OUTER JOIN b ON (a.type = b.key) |
and
|
1 2 3 |
SELECT * FROM a LEFT OUTER JOIN b ON (a.type = b.key) |
What is most confusing at first is when the (+) is on the left of the equation, you should use a RIGHT OUTER JOIN and vice versa. What this syntax says, in plain English, is: FROM a LEFT OUTER JOIN b i.e. Take the table on the LEFT of this command and join its rows with rows in the table on the right if they exist. If they don’t then just return the values from the table on the left, adding NULL values for the columns that should come from the table on the right.
Full Outer Join
A thing that can be ‘easily’ accomplished using ANSI SQL is the FULL OUTER JOIN, meaning you want the result set to contain all the records from both tables, and if a corresponding record doesn’t exist in the other table, just add NULL values for the missing columns.
|
1 2 3 4 |
SELECT * FROM a ,b WHERE a.type(+) = b.key(+) |
This syntax is unfortunately not supported by Oracle SQL. You could mimic this behavior by using a left outer join UNIONed by a right outer join:
|
1 2 3 4 5 6 7 8 9 |
SELECT * FROM a ,b WHERE a.type(+) = b.key UNION SELECT * FROM a ,b WHERE a.type = b.key(+) |
Using ANSI SQL this can be done in a single statement
|
1 2 3 |
SELECT * FROM a FULL OUTER JOIN b ON (a.type = b.key) |
On two simple tables the union can easily be used, but imagine your real world query consisting of dozens of tables where just a couple should be outer joined both ways. Copy-Paste will come in handy here, but think about a change in the specification. Chances are you will forget to implement the change in one of the queries.
Conclusion
If you are not yet using ANSI SQL, I think you should make yourself familiar with it as soon as possible. Your SQL will be vendor independent (like you would ever want to move away from Oracle) and your statements are more self-documenting, relieving you of the duty of documenting your statements. Another thing is it’s easier to explain to your Java or PHP (or…) developing colleagues when you are using ANSI SQL. It’s likely that they are more familiar with the ANSI SQL syntax, since it can also be used in, for instance, MySQL.
Load comments